#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024-01-26 9:44
# @Author  : Zhum
# @File    : sikuyiDecrypt.py
import binascii
import json
import os
import sys
import time
import openpyxl
import requests
from Crypto.Cipher import AES
from Crypto.Util.Padding import unpad
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter


def list_data_to_search(path):
    wb = openpyxl.load_workbook(path)
    sheets = wb.sheetnames
    print("请选择要查询的sheet页:")
    for i, sheet in zip(range(1, len(sheets) + 1), sheets):
        print(f"{i}. {sheet}")
    num = input("请输入序号:\n")
    return wb[sheets[int(num) - 1]]


def generate_url_list(ws):
    url_list = []
    for i in range(2, ws.max_row + 1):
        ry_name = ws.cell(row=i, column=1).value.strip()
        ry_cardno = ws.cell(row=i, column=2).value.strip()
        gen_url = f"https://jzsc.mohurd.gov.cn/APi/webApi/dataservice/query/staff/list?" \
                  f"ry_name={ry_name}&ry_cardno={ry_cardno}&pg=0&pgsz=15&total=0"
        url_list.append(gen_url)
    return url_list


def get_page_data(url_g_list):
    headers = {"Content-Type": "application/json",
               "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 ("
                             "KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 "
                             "Edg/120.0.0.0"}
    reg_ori_data = []
    for url_gen in url_g_list:
        try:
            resp = requests.get(url=url_gen, headers=headers, timeout=10)
            resp.encoding = 'utf-8'
            reg_ori_data.append(resp.text)
            time.sleep(1)
        except requests.exceptions.ConnectionError as cE:
            print("网络连接出现错误")
            print(cE)
            break
        except requests.exceptions.Timeout as tE:
            print("请求超时")
            print(tE)
            break
        except requests.exceptions.HTTPError as hE:
            print("HTTP错误")
            print(hE)
            break
        except requests.exceptions.RequestException as rE:
            print("请求失败")
            print(rE)
            break
        except ValueError as vE:
            print("ValueError错误")
            print(vE)
            break
    return reg_ori_data


def decrypt(page_data):
    KEY = 'jo8j9wGw%6HbxfFn'.encode()
    IV = '0123456789ABCDEF'.encode()

    cipher = AES.new(key=KEY, mode=AES.MODE_CBC, iv=IV)
    decrypted_data = cipher.decrypt(binascii.a2b_hex(page_data))
    return unpad(decrypted_data, block_size=AES.block_size).decode('utf8')


def solve_data(ws_s, ori_data):
    reg_info_list = []
    reg_non_list = []
    for i, data_item in zip(range(2, ws_s.max_row + 1), ori_data):
        solved_data = decrypt(data_item)
        json_ry_list = json.loads(solved_data)["data"]["list"]
        if not json_ry_list:
            non_reg = [ws_s.cell(row=i, column=1).value.strip(), ws_s.cell(row=i, column=2).value.strip()]
            reg_non_list.append(non_reg)
        elif len(json_ry_list) == 1:
            new_reg = [json_ry_list[0]["RY_NAME"], json_ry_list[0]["RY_CARDNO"],
                       json_ry_list[0]["REG_TYPE_NAME"], json_ry_list[0]["REG_SEAL_CODE"]]
            reg_info_list.append(new_reg)
        elif len(json_ry_list) > 1:
            for j in range(len(json_ry_list)):
                new_reg_more = [json_ry_list[j]["RY_NAME"], json_ry_list[j]["RY_CARDNO"],
                                json_ry_list[j]["REG_TYPE_NAME"], json_ry_list[j]["REG_SEAL_CODE"]]
                reg_info_list.append(new_reg_more)
    return reg_info_list, reg_non_list


def set_column(ws_name):
    for i in range(1, ws_name.max_column + 1):
        ws_name.column_dimensions[openpyxl.utils.get_column_letter(i)].width = 20


def write_data(reg_info_list, reg_non_list, file_path_save):
    have_non_count = 0
    have_none_list = []
    wb_new = openpyxl.Workbook()
    ws_new_have = wb_new.active
    align = Alignment(horizontal='center', vertical='center')
    ws_new_have.title = "有注册结果"
    ws_new_have.cell(row=1, column=1).value = "姓名"
    ws_new_have.cell(row=1, column=1).alignment = align
    ws_new_have.cell(row=1, column=2).value = "身份证号"
    ws_new_have.cell(row=1, column=2).alignment = align
    ws_new_have.cell(row=1, column=3).value = "注册类型"
    ws_new_have.cell(row=1, column=3).alignment = align
    ws_new_have.cell(row=1, column=4).value = "注册码"
    ws_new_have.cell(row=1, column=4).alignment = align
    ws_new_have.cell(row=1, column=5).value = "无注册码标注"
    ws_new_have.cell(row=1, column=5).alignment = align
    set_column(ws_new_have)

    ws_new_none = wb_new.create_sheet(title="未查询到结果")
    ws_new_none.cell(row=1, column=1).value = "姓名"
    ws_new_none.cell(row=1, column=2).value = "身份证号"
    set_column(ws_new_none)

    ws_have_non = wb_new.create_sheet(title="无注册码")
    ws_have_non.cell(row=1, column=1).value = "姓名"
    ws_have_non.cell(row=1, column=1).alignment = align
    ws_have_non.cell(row=1, column=2).value = "身份证号"
    ws_have_non.cell(row=1, column=2).alignment = align
    set_column(ws_have_non)

    for i, reg_info in zip(range(2, len(reg_info_list) + 2), reg_info_list):
        print(f"{ws_new_have}的第{i - 1}行")
        print(f"{reg_info[0]}的注册信息正在写入")
        ws_new_have.cell(row=i, column=1).value = reg_info[0]

        ws_new_have.cell(row=i, column=2).value = reg_info[1]
        ws_new_have.cell(row=i, column=3).value = reg_info[2]
        ws_new_have.cell(row=i, column=4).value = reg_info[3]
        if reg_info[3] == "":
            ws_new_have.cell(row=i, column=5).value = "无注册码"
            have_non_count += 1
            have_none_list.append(reg_info)
        # 如果当前单元格的值与上一个单元格的值相同
        if ws_new_have.cell(i, 1).value == ws_new_have.cell(i - 1, 1).value or \
                ws_new_have.cell(i, 2).value == ws_new_have.cell(i - 1, 2).value:
            n = ws_new_have.cell(i, 1).column
            # 合并单元格
            ws_new_have.merge_cells(start_row=i - 1, start_column=n, end_row=i, end_column=n)
        for j in range(1, 6):
            ws_new_have.cell(row=i, column=j).alignment = align

    for i, have_non_item in zip(range(2, have_non_count + 2), have_none_list):
        ws_have_non.cell(row=i, column=1).value = have_non_item[0]
        ws_have_non.cell(row=i, column=1).alignment = align
        ws_have_non.cell(row=i, column=2).value = have_non_item[1]
        ws_have_non.cell(row=i, column=2).alignment = align
        set_column(ws_have_non)

    for i, non_reg in zip(range(2, len(reg_non_list) + 2), reg_non_list):
        print(f"\n{ws_new_none}的第{i - 1}行")
        print(f"{non_reg[0]}正在写入")
        ws_new_none.cell(row=i, column=1).value = non_reg[0]
        ws_new_none.cell(row=i, column=2).value = non_reg[1]
        for j in range(1, 5):
            ws_new_have.cell(row=i, column=j).alignment = align

    wb_new.save(file_path_save)


def main_solve(file_path):
    ws = list_data_to_search(file_path)
    url_gen_list = generate_url_list(ws)
    print("正在查询数据中，请等待...")
    origin_data = get_page_data(url_gen_list)
    if not origin_data:
        print("没有查询到数据，请检查网络连接或输入的姓名和证件号是否正确")
        exit(0)
    else:
        have_reg_info, non_reg_info = solve_data(ws, origin_data)
        print("已经完成查询，正在保存数据到文件中...")
        filename = file_path.strip().split("\\")[-1]
        save_path = file_path.replace(filename, f"sikuyi-{time.strftime('%Y-%m-%d-%H-%M')}.xlsx")
        try:
            write_data(have_reg_info, non_reg_info, save_path)
        except Exception as e:
            print("保存失败，请检查文件路径是否正确")
            print(e)
        time.sleep(2)
        print("\n\n----------保存成功!!!!------------")
        print(
            f"共查询{len(have_reg_info)}+{len(non_reg_info)}信息,"
            f"{len(have_reg_info)}条信息可以查询到,{len(non_reg_info)}条信息无法查询到。")
        print(f"\n文件已保存在{save_path}下,请查看\n")


def center_text(text, fill_type=" "):
    print("\n")
    print(text.center(60, fill_type))


if __name__ == '__main__':
    print("\n")
    center_text("", "=")
    center_text("欢迎使用sikuyi", "=")
    center_text("本程序用于查询四库一平台人员信息", "=")
    process_1 = "正在启动"
    center_text(process_1, "-")
    time.sleep(3)
    if len(sys.argv) == 1:
        center_text("请拖入或者输入需要查询的excel文件并按下Enter按键", ".")
        file_name = input("拖入or输入的文件地址:\n").replace("\"", "").strip()
        if file_name == "":
            center_text("文件路径错误，请重新输入")
            os.execl(sys.executable, sys.executable, *sys.argv)
        else:
            main_solve(file_name)
    else:
        file_name = sys.argv[1]
        if file_name == "":
            center_text("文件路径错误，请重新输入")
            os.execl(sys.executable, sys.executable, *sys.argv)
        else:
            main_solve(file_name)

    center_text("please press any key to exit...")
    input()
    exit(0)
